package com.example.studentcoursesysteam.Repository;

import com.example.studentcoursesysteam.Bean.StudentCourse;
import com.example.studentcoursesysteam.Vo.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface StudentCourseRepository extends JpaRepository<StudentCourse,Integer> {

    boolean existsByStudentCodeAndCourseCode(String studentCode,String courseCode);

    boolean existsByStudentCode(String studentCode);

    boolean existsByCourseCode(String courseCode);

    @Transactional
    @Modifying
    @Query(value = "delete from student_course where student_code=:studentCode ",nativeQuery = true)
    Integer deleteStudentId(String studentCode);

    @Transactional
    @Modifying
    @Query(value = "delete from student_course where student_code=:studentCode and course_code=:courseCode",nativeQuery = true)
    Integer deleteCourse(String studentCode,String courseCode);

    @Transactional
    @Modifying
    @Query(value = "update student_course set achievement=:achievement where student_code=:studentCode and course_code=:courseCode ",nativeQuery = true)
    Integer updateStatus(Float achievement,String studentCode,String courseCode);

    @Query(value="select s.code as studentCode ,avg(sc.achievement) as averageAchievement " +
            "from student_course sc,student s where sc.student_code=s.code and sc.student_code=:studentCode",nativeQuery = true)
    List<AchievementVo> selectAchievement(String studentCode);

    @Query(value="SELECT s.code AS studentCode ,s.name AS studentName,c.code AS courseCode,c.name AS courseName,sc.achievement " +
            "FROM student_course sc,course c,student s WHERE sc.student_code=s.code AND c.code=sc.course_code " +
            "AND sc.student_code=:studentCode",nativeQuery = true)
    List<StudentAchievementVo> selectCAchievement(String studentCode);

    @Query(value = "select s.code as studentCode,s.name as studentName,c.code as courseCode,c.name as courseName , sc.achievement " +
            "from student s,course c, student_course sc where s.code=:studentCode and sc.course_code= c.code and  sc.student_code=:studentCode order by sc.achievement ",nativeQuery = true)
    List<StudentCourseVo> selectStudentCourse(String studentCode);

    @Query(value = "select s.code as studentCode,s.name as studentName,s.sex as studentSex,s.department as " +
            "studentDepartment,c.code as courseCode,c.name as courseName , sc.achievement ,sc.select_date as selectDate " +
            "from student s,course c, student_course sc " +
            "where s.code=sc.student_code and sc.course_code= c.code and c.name=:name order by s.code",nativeQuery = true)
    Page<StudentCourseNameVo> selectStudentCourseName(String name, Pageable pageable);

    @Query(value = "select c.code as courseCode,c.name as courseName,avg(sc.achievement) as averageAchievement," +
            "max(sc.achievement) as maxAchievement,min(sc.achievement) as minAchievement from course c, student_course sc " +
            "where c.code=sc.course_code and c.name=:name group by c.code",nativeQuery = true)
    List<CourseMaxMinAvgVo> selectStudentCourseAchievement(String name);

    @Query(value = "select c.code as courseCode,c.name as courseName,avg(sc.achievement) as averageAchievement,max(sc.achievement) " +
            "as maxAchievement,min(sc.achievement) as minAchievement from course c,student s, student_course sc " +
            "where s.code=sc.student_code and c.code=sc.course_code and sc.student_code=:studentCode group by c.code",nativeQuery = true)
    List<CourseMaxMinAvgVo> selectStudentCourseAchievementCode(String studentCode);

    List<StudentCourse> findByStudentCode(String studentCode);
}
